Previously we went over creating your own reports from the data that ShopSite collects via orders. Another source of information is registered customers. A question that comes up for both of these is how to deal with the date information provided by ShopSite. Unfortunately Excel does not understand this date format so we’ll have to massage it a little to keep Excel (and other spreadsheets) happy.

ShopSite provides us with the date in this format:

Mon Jun  1 13:05:17 2015  =  Day of Week   Month   Day of Month   24hr Time   Year

Looking at the data we see there are five fields. I’m assuming you have the data loaded into a spreadsheet and are ready to proceed. In order to do so we need to give ourselves a little elbow room to work. To the right of the date column you want to convert, insert five columns. Don’t worry, Excel should warn you before overwriting data that may be in the way.

Break it to fix it

In order to change the date format, we’ll need to break it apart. Excel has a useful tool called “Text to Columns.” This feature is also available in LibreOffice but doesn’t appear to be available in Apple Numbers. Mac users will need to find a work around to make this happen unfortunately.

“Text to Columns”  takes a string and breaks it apart based on delimiters. We’re going to use this tool to break the date down so we can recreate it in a format that Excel understands. We’ve already made room for the data we’re going to create so the next step is to select the data itself. Once that is done, open the Data ribbon and select “Text to Columns.” For this data we’ll use Delimited with a space being the delimiter. Once you hit Next you will see a preview of how the data will be parsed and can adjust this if necessary. Clicking on Next again will allow you to set the format for the different columns. Leaving all the columns as General will be fine for our purposes. Click Finish and the date will be split into individual columns.

You should now see the data string broken up into multiple columns. If you look at the data you’ll see that the time appears in between the day of the month and the year. If you don’t need to use the time you can simply delete this column. If you do, then use the extra blank column we created earlier as swap space for flipping the time and year columns. Surprisingly Excel does not have a built in function to swap the columns. This can be automated via programming if you are really interested in doing so. A Google search will turn up a few methods of doing this.

Reassembly required

Now that the date string has been broken apart, it’s time to reassemble it in a format Excel can work with. The first step will be to reformat the month so that Excel can understand it. To do this we are going to use the formula:

=MONTH(“1″&xn)

where xn is the cell the containing the data you wish to convert. This formula tricks Excel into thinking the three digit text string is a month by appending a one to the string making it something like 1Oct and then converting it into the numeric value for the month. Add a column to the right of the month column and add the formula.

To put everything back together we’ll use the Date function. The format of the function is:

=DATE(Cyear,Cmonth,Cday)

where Cyear stands for the cell the year data is in, etc. Note that in our case the data appears as month, day, year. Make sure you enter the cell locations in the order Excel is looking for. Once that’s done copy the formula to the other cells and you now have the date in a format Excel is comfortable with.

Dealing with time

While it is possible to add the time to the date and make it into one field it is probably not worth the extra effort. The field is already in a format that Excel understands and if you do need to search by date and time you would simply set the time as the secondary search criteria.

Clean Up

The extra column we inserted earlier on can be used to copy the time data into so that we can clean up the spreadsheet. One thing to keep in mind when moving things around is that since we’ve used formulas you can’t simply cut and paste to move the newly created date around. You’ll want to use a special paste called Paste Values. This will paste the date you created without having to keep the data for the formula that created it intact.